DB2-specific Geography Tasks
IN THIS PAGE
Description
DB2 examples for creating and dropping tables with geometry columns, inserting location data values, inserting line data values, and inserting polygon data values.
Enabling DB2 Spatial Extender
Before you can use geometry columns you must install DB2 Spatial Extender.
On Windows, using the Typical configuration DOES NOT install DB2 Spatial Extender.
You must also enable DB2 Spatial Extender for each database that will contain geography data.
You can use the command "db2se enable_db mydatabase" in the DB2 command line tool to do this.
In the samples below, :SRID indicates the optional spatial reference identifier argument. For DB2, the default is 1003.
Creating and Dropping Tables with Geometry Columns
Creating a table with geometry columns:
CREATE TABLE GeogTest ( KeyValue VARCHAR(25) NOT NULL, Location DB2GSE.ST_GEOMETRY NOT NULL, PRIMARY KEY (KeyValue))
Dropping a table:
DROP TABLE GeogTest
Inserting Location Data Values
Inserting location data using Portable SQL Syntax:
INSERT INTO GeogTest (KeyValue, Location) values('Item1', GeogCreateLocation(-70, 42, :SRID))
Inserting location data using Native syntax:
INSERT INTO GeogTest (KeyValue, Location) VALUES ('Item1', DB2GSE.ST_Point( CAST( - 70 AS DOUBLE), CAST(42 AS DOUBLE), CAST(:SRID AS INTEGER)))
Inserting Line Data Values
Inserting line data values using Portable SQL Syntax:
INSERT INTO GeogTest (KeyValue, Location) values('Item2', GeogCreateLine(-70, 42, -70, 38, :SRID))
Inserting line data values using Native SQL Syntax:
INSERT INTO GeogTest (KeyValue, Location) VALUES ('Item2', DB2GSE.ST_Geometry( 'LINESTRING(' || CAST( - 70 as VARCHAR(50)) || ' ' || CAST(42 as VARCHAR(50)) || ', ' || CAST( - 70 as VARCHAR(50)) || ' ' || CAST(38 as VARCHAR(50)) || ')', :SRID))
Inserting Polygon Data Values
Inserting polygon values using Portable SQL Syntax:
INSERT INTO GeogTest (KeyValue, Location) values('Item3', GeogCreatePolygon(-70, 42, -70, 32, -60, 32, -60, 42, -70, 42, :SRID))
Inserting polygon values using Native SQL Syntax:
INSERT INTO GeogTest (KeyValue, Location) VALUES ('Item3', DB2GSE.ST_Geometry('POLYGON((' || CAST( - 70 as VARCHAR(50)) || ' ' || CAST(42 as VARCHAR(50)) || ', ' || CAST( - 70 as VARCHAR(50)) || ' ' || CAST(32 as VARCHAR(50)) || ', ' || CAST( - 60 as VARCHAR(50)) || ' ' || CAST(32 as VARCHAR(50)) || ', ' || CAST( - 60 as VARCHAR(50)) || ' ' || CAST(42 as VARCHAR(50)) || ', ' || CAST( - 70 as VARCHAR(50)) || ' ' || CAST(42 as VARCHAR(50)) || '))', :SRID))
See Also
- Geographic Databases
- Changes to SQL Objects for Geometry
- Portable SQL Functions for Geographies
- Common Geography Database Tasks
- Database-specific Geography Tasks
- DB2-specific Geography Tasks
- MySQL-specific Geography Tasks
- Oracle-specific Geography Tasks
- PostgreSQL-specific Geography Tasks
- SQL Server-specific Geography Tasks
- SQL Geography Examples